﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VKDANSP
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                case "JLT":
                case "UBT":
                default:
                    #region=========
                    dwReturn["KDSMANDANT"] = app.Mandant;
                    dwReturn["KDSKUNDNR"] = "00000000";
                    dwReturn["KDSANSPNA"] = "        ";
                    dwReturn["KDSANSPNAME"] = "                              ";
                    dwReturn["KDSANREDE"] = "2";
                    dwReturn["KDSNAME1"] = "                                        ";
                    dwReturn["KDSVB"] = "   ";
                    dwReturn["KDSVG"] = "   ";
                    dwReturn["KDSFKT"] = "                                                  ";
                    dwReturn["KDSANFDAT"] = "0";
                    dwReturn["KDSENDDAT"] = "0";
                    dwReturn["KDSZUSADRSL"] = "        ";
                    dwReturn["KDSTEL"] = "                    ";
                    dwReturn["KDSFAX"] = "                    ";
                    dwReturn["KDSURLVDAT"] = "0";
                    dwReturn["KDSURLBDAT"] = "0";
                    dwReturn["KDSVERTR"] = "        ";
                    dwReturn["KDSSACHB"] = "        ";
                    dwReturn["KDSWVDAT"] = "0";
                    dwReturn["KDSWVGRUND"] = "                         ";
                    dwReturn["KDSGEBJJ"] = "0000";
                    dwReturn["KDSGEBMM"] = "00";
                    dwReturn["KDSGEBTT"] = "00";
                    dwReturn["KDSVIPGRP"] = "        ";
                    dwReturn["KDSPRAESTYP"] = "   ";
                    dwReturn["KDSWEIHKKZ"] = "N";
                    dwReturn["KDSSPERRPJ"] = "N";
                    dwReturn["KDSMAILADR"] = "                                                                                                    ";
                    dwReturn["KDSPASSW"] = "                    ";
                    dwReturn["KDSFILLER"] = "                                                                               ";
                    dwReturn["KDSAENDZL"] = "0";
                    dwReturn["KDSAENDBEN"] = "        ";
                    dwReturn["KDSAENDDAT"] = "0";
                    dwReturn["KDSNEUBEN"] = "        ";
                    dwReturn["KDSNEUDAT"] = "0";
                    #endregion
                    break;
            }
        }
     
        public void InsertKDANSP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            dwData["KDSNEUBEN"] = app.User;
            dwData["KDSNEUDAT"] = date;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                case "SXN":
                case "TST":
                case "UBT":
                default:
                    or1.CommandText = "INSERT INTO KDANSP(KDSMANDANT, KDSKUNDNR, KDSANSPNA, KDSANSPNAME, KDSANREDE, KDSNAME1, KDSVB, KDSVG, KDSFKT, KDSANFDAT, KDSENDDAT, KDSZUSADRSL, KDSTEL, KDSFAX, KDSURLVDAT, KDSURLBDAT, KDSVERTR, KDSSACHB, KDSWVDAT, KDSWVGRUND, KDSGEBJJ, KDSGEBMM, KDSGEBTT, KDSVIPGRP, KDSPRAESTYP, KDSWEIHKKZ, KDSSPERRPJ, KDSMAILADR, KDSPASSW, KDSFILLER, KDSAENDZL, KDSAENDBEN, KDSAENDDAT, KDSNEUBEN, KDSNEUDAT)"
                    + "VALUES(:KDSMANDANT:,:KDSKUNDNR:,:KDSANSPNA:,:KDSANSPNAME:,:KDSANREDE:,:KDSNAME1:,:KDSVB:,:KDSVG:,:KDSFKT:,:KDSANFDAT:,:KDSENDDAT:,:KDSZUSADRSL:,:KDSTEL:,:KDSFAX:,:KDSURLVDAT:,:KDSURLBDAT:,:KDSVERTR:,:KDSSACHB:,:KDSWVDAT:,:KDSWVGRUND:,:KDSGEBJJ:,:KDSGEBMM:,:KDSGEBTT:,:KDSVIPGRP:,:KDSPRAESTYP:,:KDSWEIHKKZ:,:KDSSPERRPJ:,:KDSMAILADR:,:KDSPASSW:,:KDSFILLER:,:KDSAENDZL:,:KDSAENDBEN:,:KDSAENDDAT:,:KDSNEUBEN:,:KDSNEUDAT:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }

        public DataSet QueryKDANSP(DataRow dwData, int option = 1)
        {
           
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM KDANSP WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    SResult = QKSP01(dwData);
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM KDANSP  WHERE KDSMANDANT=:KDSMANDANT: AND  KDSKUNDNR = :KDSKUNDNR: AND KDSANSPNA=:KDSANSPNA:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM KDANSP  JOIN VKKOPF ON KDSMANDANT=VKKMANDANT AND KDSKUNDNR=VKKKUNDNR WHERE VKKMANDANT=:VKKMANDANT: AND VKKAUFKRS=:VKKAUFKRS: AND VKKAUFLFD=:VKKAUFLFD:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 4:
                    or1.CommandText = "SELECT DISTINCT VVLIEFSCH,C.* FROM VKPOS P JOIN VKKOPF ON VKKAUFKRS=VKPAUFKRS AND VKKAUFLFD=VKPAUFLFD JOIN VVTLIEF ON VVTAUFTYP='K' AND VKPSA='  ' AND VVTAUFKRS=VKPAUFKRS AND VVTAUFLFD=VKPAUFLFD AND VVTAUFPOS=VKPAUFPOS  JOIN VVLIEF ON VVLIEFSCH=VVTLIEFSCH   JOIN KDANSP C ON VKKKUNDNR=KDSKUNDNR AND VKKANSPRECH=KDSANSPNA "
                    + " WHERE  VVMANDANT=:VVMANDANT: AND VVLIEFSCH=:VVLIEFSCH:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 5:
                    or1.CommandText = "SELECT * FROM KDANSP  WHERE KDSMANDANT=:KDSMANDANT: "
                        + dwData["KDSKUNDNR"].ToString()
                     +" AND KDSANSPNA LIKE '%" + dwData["KDSANSPNA"].ToString() + "%' AND KDSNAME1 LIKE '%" + dwData["KDSNAME1"].ToString() + "%'";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;

                case -1:
                    or1.CommandText = "SELECT KDSANSPNA,KDSKUNDNR,KDSANSPNA AS KEY,KDSANSPNA AS DES FROM KDANSP   WHERE  KDSKUNDNR = :KDSKUNDNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
            }
           
            
                SResult.Tables[0].TableName = "KDANSP";
            

            return SResult;
        }
        public DataSet QKSP01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM KDANSP   WHERE  KDSMANDANT=:KDSMANDANT: AND  KDSKUNDNR = :KDSKUNDNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            return SResult;
        }
        public void DeleteKDANSP(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    DKDA01(dwData);
                    break;
                case 2:
                    DKDA02(dwData);
                    break;
                default:
                    break;
            }
          
        }
        public void DKDA00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM KDANSP   WHERE  KDSMANDANT=:KDSMANDANT: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DKDA01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM KDANSP   WHERE  KDSMANDANT=:KDSMANDANT: AND KDSKUNDNR=:KDSKUNDNR: AND KDSANSPNA =:KDSANSPNA:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DKDA02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM KDANSP   WHERE  KDSMANDANT=:KDSMANDANT: AND KDSKUNDNR=:KDSKUNDNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }

        public void UpdateKDANSP(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            if (dwData != null)
            {
                dwData["KDSAENDBEN"] = app.User;
                dwData["KDSAENDDAT"] = date;
            }
          
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command====
                    or1.CommandText = "UPDATE KDANSP SET "
                    + "KDSMANDANT= :KDSMANDANT:,"
                    + "KDSKUNDNR= :KDSKUNDNR:,"
                    + "KDSANSPNA= :KDSANSPNA:,"
                    + "KDSANSPNAME= :KDSANSPNAME:,"
                    + "KDSANREDE= :KDSANREDE:,"
                    + "KDSNAME1= :KDSNAME1:,"
                    + "KDSVB= :KDSVB:,"
                    + "KDSVG= :KDSVG:,"
                    + "KDSFKT= :KDSFKT:,"
                    + "KDSANFDAT= :KDSANFDAT:,"
                    + "KDSENDDAT= :KDSENDDAT:,"
                    + "KDSZUSADRSL= :KDSZUSADRSL:,"
                    + "KDSTEL= :KDSTEL:,"
                    + "KDSFAX= :KDSFAX:,"
                    + "KDSURLVDAT= :KDSURLVDAT:,"
                    + "KDSURLBDAT= :KDSURLBDAT:,"
                    + "KDSVERTR= :KDSVERTR:,"
                    + "KDSSACHB= :KDSSACHB:,"
                    + "KDSWVDAT= :KDSWVDAT:,"
                    + "KDSWVGRUND= :KDSWVGRUND:,"
                    + "KDSGEBJJ= :KDSGEBJJ:,"
                    + "KDSGEBMM= :KDSGEBMM:,"
                    + "KDSGEBTT= :KDSGEBTT:,"
                    + "KDSVIPGRP= :KDSVIPGRP:,"
                    + "KDSPRAESTYP= :KDSPRAESTYP:,"
                    + "KDSWEIHKKZ= :KDSWEIHKKZ:,"
                    + "KDSSPERRPJ= :KDSSPERRPJ:,"
                    + "KDSMAILADR= :KDSMAILADR:,"
                    + "KDSPASSW= :KDSPASSW:,"
                    + "KDSFILLER= :KDSFILLER:,"
                    + "KDSAENDZL= :KDSAENDZL:,"
                    + "KDSAENDBEN= :KDSAENDBEN:,"
                    + "KDSAENDDAT= :KDSAENDDAT:"
                        + " WHERE  "
                        + "  KDSMANDANT=:KDSMANDANT: AND KDSKUNDNR=:KDSKUNDNR: AND KDSANSPNA =:KDSANSPNA:";
                    #endregion 
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                case 2:
                    #region====Command====
                    or1.CommandText = "UPDATE KDANSP SET "
                    + "KDSKUNDNR= :KDSANSPNA:" 
                        + " WHERE  "
                        + "  KDSMANDANT=:KDSMANDANT: AND KDSKUNDNR=:KDSKUNDNR:";
                    #endregion 
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                default:
                    break;
            } 

            Connect2Proxy(or1);
        }
    }
}
